set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Stored Procedure

ALTER PROCEDURE [dbo].[sprTongHopCacChuongTrinhDaiLy]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
	@Year INT,
	@Month INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--DECLARE @vReportDate DATETIME
	--SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID
	

	CREATE TABLE #tblTongKetCacChuongTrinh
	(
		ID INT IDENTITY (1,1),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		ChietKhauThangTheoCT MONEY,
		ChietKhauThangDaThucHien MONEY,
		ThanhToanNhanhTheoChuongTrinh MONEY,
		ThanhToanNhanhDaThucHien MONEY,
		ChuongTrinhATAFASTDaily MONEY,
		DaThuATAFASTDaily MONEY,
		ChuongTrinhATAFASTNhaThau MONEY,
		DaThuATAFASTNhaThau MONEY,
		ChuongTrinhMayPhaMauViTinh MONEY,
		DaThuChuongTrinhMayPhaMauViTinh MONEY,
		ChuongTrinhHoTroNhanVien MONEY,
		ChuongTrinhHoTroNam MONEY, 
		TongTheoChuongTrinh MONEY,
		DaTra MONEY
	)
	DECLARE @ChietKhauID UNIQUEIDENTIFIER
	DECLARE @TransactionFee float
	SET @TransactionFee = 0.5

	DECLARE ChietKhauCursor CURSOR FOR SELECT ChietKhauID FROM dbo.ChietKhau
	OPEN ChietKhauCursor
	FETCH NEXT FROM ChietKhauCursor
	INTO @ChietKhauID

	WHILE @@FETCH_STATUS = 0
	BEGIN
			SELECT A.ChietKhauID, MucThuong, SanPhamID, DoanhSo, StartDate, EndDate, Header as ChuongTrinhChietKhau, ToanTuSoSanh
			INTO #tblDiscount
			FROM dbo.ChietKhau A, dbo.ChietKhauDoanhSo B, dbo.DieuKienGiamGia C, dbo.NhomSanPham D, dbo.LoaiThoiGian E
			WHERE
			A.DieuKienGiamGiaID = C.DieuKienID
			and B.NhomSanPhamID = D.NhomSanPhamID
			and B.LoaiThoiGianID = E.LoaiThoiGianID
			and B.ChietKhauID = A.ChietKhauID
			and TenLoaiThoiGian = 'Month' and C.IsActive = 1
			AND A.ChietKhauID = @ChietKhauID
			--
			DECLARE @NhanHang NVARCHAR(100)
			IF (EXISTS(SELECT ToanTuSoSanh From #tblDiscount WHERE ToanTuSoSanh IS NOT NULL))
				BEGIN 
					SET @NhanHang = (SELECT TOP 1 SanPhamID FROM #tblDiscount)
				END 
			ELSE 
				BEGIN
					SET @NhanHang = (SELECT TOP 1 SUBSTRING(SanPhamID, 1, CHARINDEX('-', SanPhamID) - 1) FROM #tblDiscount)
				END 

			CREATE TABLE #tblMonthlyDiscountReport
			(
				ID INT IDENTITY(1, 1),
				KhachHangID UNIQUEIDENTIFIER,
				MaKH NVARCHAR(100),
				TenKH NVARCHAR(100),
				[DS-TD] MONEY,
				[DS-CC] MONEY,
				[CK-TD] MONEY,
				[CK-CC] MONEY,
				[ThanhToanTrongThang] MONEY,
				[ChietKhauThanhToanTienNhanh] MONEY,
				[HoTroVanChuyen] MONEY, 
				[HoTroKhac] MONEY,
				[PhiChuyenKhoan] MONEY, 
				[TongGiamTru] MONEY,
				[ThuongCKTheoCT] MONEY,
				[ThanhToanNhanhTheoCT] MONEY,
				PhanLoaiHang NVARCHAR(100),
				TenHang NVARCHAR(100),
				DonVi NVARCHAR(100),
				DoanhSoTrongThang money,
				SoLuong int,
				SoHoaDon NVARCHAR(100),
				TienThanhToan money,
				TienChuaThanhToan money,
				NgayThanhToan DATETIME,
				NgayHoaDon DATETIME,
				MaHang NVARCHAR(100),
				ShippingMethodCode nvarchar(20),
				AccountingObjectGroupCode nvarchar(20),
				RefId UNIQUEIDENTIFIER
			)


			INSERT INTO #tblMonthlyDiscountReport(KhachHangID, MaKH, TenKH, MaHang, PhanLoaiHang, Tenhang, DoNVi, DoanhSoTrongThang, SoLuong, SoHoaDon, TienThanhToan, TienChuaThanhToan, NgayThanhToan, NgayHoaDon, ShippingMethodCode, AccountingObjectGroupCode, RefId)
			SELECT ao.AccountingObjectID AS KhachHangID,
					ao.AccountingObjectCode as MaKH, 
					ao.AccountingObjectName as TenKH,
					ii.InventoryItemCode AS MaHang,
					ISNULL(iic.InventoryCategoryCode, '') as PhanLoaiHang, 
					ii.InventoryItemName as TenHang, 
					ii.Unit as DonVi,   
					(vfp.Amount + vfp.VATAmount) as DoanhSoTrongThang,
					vfp.Quantity AS SoLuong, 
					vfp.RefNo as SoHoaDon,
					SUM(ISNULL(cardc.Amount, 0)) AS TienThanhToan, 
					SUM(ISNULL(cardc.ReceipableAmount, 0)) AS TienChuaThanhToan,
					car.RefDate as NgayThanhToan,
					vfp.PostedDate as NgayHoaDon,
					sm.ShippingMethodCode,
					AccountingObjectGroupCode,
					vfp.RefId
			FROM	[ATAHANOI]..View_VoucherForPayable vfp
			INNER JOIN [ATAHANOI]..InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
			INNER JOIN [ATAHANOI]..accountingObject ao ON vfp.AccountingObjectID = ao.AccountingObjectID
			LEFT JOIN [ATAHANOI]..AccountingObjectGroup aog ON ao.AccountingObjectGroupID = aog.AccountingObjectGroupID
			LEFT JOIN [ATAHANOI]..InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID
			LEFT JOIN [ATAHANOI]..CAReceiptDetailCustomer cardc ON vfp.RefId = cardc.SaleInvoiceRefId
			LEFT JOIN [ATAHANOI]..CAReceipt car ON cardc.RefId = car.RefId
			LEFT JOIN [ATAHANOI]..SAInvoice sa ON vfp.RefID = sa.RefId
			LEFT JOIN [ATAHANOI]..ShippingMethod sm ON sa.ShippingMethodID = sm.ShippingMethodId
			WHERE
					ao.IsCustomer = 1 AND ao.IsEmployee = 0
					AND (ao.BranchID = @BranchID OR aog.AccountingObjectGroupCode LIKE '%-' + @TrungTamCode + '-%')
					AND (vfp.CreditAccount LIKE '511%' and vfp.DebitAccount LIKE '131%') 
					AND ao.AccountingObjectCode NOT IN (SELECT BranchCode FROM [ATAHANOI]..Branch)
					AND vfp.Posted=1
					AND (DATEPART(month, vfp.PostedDate) = @Month AND DATEPART(year, vfp.PostedDate) = @Year)
					AND ISNULL(iic.InventoryCategoryCode, '') LIKE '%' + @NhanHang + '%'
			GROUP BY vfp.Amount, ao.AccountingObjectID, ao.AccountingObjectCode, vfp.RefId, aog.AccountingObjectGroupCode, sm.ShippingMethodCode, ao.AccountingObjectName, ii.InventoryItemID, ii.InventoryItemCode, iic.InventoryCategoryCode, ii.InventoryItemName, ii.Unit, vfp.RefNo, car.RefDate, vfp.Quantity, vfp.VATAmount, vfp.PostedDate
			ORDER BY TenKH, InventoryCategoryCode

			UPDATE #tblMonthlyDiscountReport
			SET SoLuong = CASE 
							WHEN DonVi = 'L1L' THEN SoLuong / 6
							WHEN DonVi = 'L4L' THEN SoLuong / 4
							WHEN DonVi = 'L4.5L' THEN SoLuong / 4
							WHEN DonVi = 'L5L' THEN SoLuong / 4
							ELSE SoLuong END

			UPDATE #tblMonthlyDiscountReport
			SET [HoTroVanChuyen] = CASE ShippingMethodCode 
									WHEN '02' THEN CASE WHEN SoLuong >= 30 THEN DoanhSoTrongThang * 7 / 100 END
									WHEN '01' THEN CASE WHEN CHARINDEX('V1', AccountingObjectGroupCode) > 0 THEN CASE WHEN SoLuong >= 600 THEN DoanhSoTrongThang * 12 / 100
																												  WHEN SoLuong >= 30 THEN DoanhSoTrongThang * 7 / 100 END
														WHEN CHARINDEX('V2', AccountingObjectGroupCode) > 0 THEN CASE WHEN SoLuong >= 600 THEN DoanhSoTrongThang * 22 / 100
																												  WHEN SoLuong >= 30 THEN DoanhSoTrongThang * 14 / 100 END
														WHEN CHARINDEX('V3', AccountingObjectGroupCode) > 0 THEN CASE WHEN SoLuong >= 600 THEN DoanhSoTrongThang * 32 / 100
																												  WHEN SoLuong >= 30 THEN DoanhSoTrongThang * 22 / 100 END
													END
									END 

			--IF NOT EXISTS (SELECT * From #tblDiscount)
			--	BEGIN 
			--		RETURN 0;
			--	END
			IF (EXISTS(SELECT ToanTuSoSanh From #tblDiscount WHERE ToanTuSoSanh IS NOT NULL))
				BEGIN 
					CREATE TABLE #tblMonthlyDiscountReportByCategory
					(
						ID INT IDENTITY(1, 1),
						MaKH NVARCHAR(100),
						TenKH NVARCHAR(100),
						[DS-TD] MONEY,
						[DS-CC] MONEY,
						[CK-TD] MONEY,
						[CK-CC] MONEY,
						[ThanhToanTrongThang] MONEY,
						[ChietKhauThanhToanTienNhanh] MONEY,
						[HoTroVanChuyen] MONEY, 
						[HoTroKhac] MONEY,
						[PhiChuyenKhoan] MONEY, 
						[TongGiamTru] MONEY,
						PhanLoaiHang NVARCHAR(100),
						DoanhSoTrongThang money
					)

					UPDATE #tblMonthlyDiscountReport
					SET
					[ChietKhauThanhToanTienNhanh] = (CASE WHEN ([TienThanhToan] >= TienChuaThanhToan AND DATEDIFF(hour, NgayHoaDon, NgayThanhToan) < 24) THEN DoanhSoTrongThang * 5 / 100 ELSE 0 END ) 

					INSERT INTO #tblMonthlyDiscountReportByCategory(MaKh, TenKH, [ChietKhauThanhToanTienNhanh], PhanLoaiHang, DoanhSoTrongThang, HoTroVanChuyen)
					SELECT MaKH, TenKH, SUM(ChietKhauThanhToanTienNhanh), PhanLoaiHang, SUM(DoanhSoTrongThang), SUM(HoTroVanChuyen)
					FROM #tblMonthlyDiscountReport
					GROUP BY MaKH, TenKH, PhanLoaiHang

					DECLARE @DoanhSoTrongThang money
					DECLARE @ID int

					DECLARE DoanhSoCursor CURSOR FOR SELECT DoanhSoTrongThang, ID FROM #tblMonthlyDiscountReportByCategory
					OPEN DoanhsoCursor
					FETCH NEXT FROM DoanhsoCursor
					INTO @DoanhSoTrongThang, @ID

					WHILE @@FETCH_STATUS = 0
					BEGIN
						DECLARE @ToanTuSoSanh nvarchar(100)
						DECLARE @MucThuong int
						DECLARE @DoanhSo money
						DECLARE @ChietKhau int

						DECLARE ChietKhauCursor CURSOR FOR SELECT ToanTuSoSanh, MucThuong, DoanhSo FROM #tblDiscount ORDER BY DoanhSo DESC
						OPEN ChietKhauCursor
						FETCH NEXT FROM ChietKhauCursor
						INTO @ToanTuSoSanh, @MucThuong, @DoanhSo 

						WHILE @@FETCH_STATUS = 0
							BEGIN 
								IF (@ToanTuSoSanh = '<')
									BEGIN 
										IF (@DoanhSoTrongThang < @DoanhSo)
											SET @ChietKhau = @MucThuong
									END
								ELSE IF (@ToanTuSoSanh = '<=')
									BEGIN 
										IF (@DoanhSoTrongThang <= @DoanhSo)
											SET @ChietKhau = @MucThuong
									END
								ELSE IF (@ToanTuSoSanh = '=')
									BEGIN 
										IF (@DoanhSoTrongThang = @DoanhSo)
											SET @ChietKhau = @MucThuong
									END
								ELSE IF (@ToanTuSoSanh = '>=')
									BEGIN 
										IF (@DoanhSoTrongThang >= @DoanhSo)
											SET @ChietKhau = @MucThuong
									END
								ELSE IF (@ToanTuSoSanh = '>')
									BEGIN 
										IF (@DoanhSoTrongThang > @DoanhSo)
											SET @ChietKhau = @MucThuong
									END

								FETCH NEXT FROM ChietKhauCursor
								INTO @ToanTuSoSanh, @MucThuong, @DoanhSo
							END

							CLOSE ChietKhauCursor;
							DEALLOCATE ChietKhauCursor;

						print 'chietkhau: ' + CAST(@chietkhau AS nvarchar(10))
						print 'doanhsotrongthang: ' + CAST(@DoanhSoTrongThang AS nvarchar(100))

						UPDATE #tblMonthlyDiscountReportByCategory
						SET [CK-TD] = @DoanhSoTrongThang * @ChietKhau / 100
						WHERE ID = @ID

						print @@ROWCOUNT

						FETCH NEXT FROM DoanhsoCursor
						INTO @DoanhSoTrongThang, @ID
					END

					CLOSE DoanhSoCursor;
					DEALLOCATE DoanhSoCursor;

					DELETE FROM #tblMonthlyDiscountReport

					INSERT INTO #tblMonthlyDiscountReport(MaKh, TenKH, [ChietKhauThanhToanTienNhanh], [CK-TD], PhanLoaiHang, DoanhSoTrongThang, [DS-TD], HoTroVanChuyen)
					SELECT MaKH, TenKH, ChietKhauThanhToanTienNhanh, [CK-TD], PhanLoaiHang, DoanhSoTrongThang, DoanhSoTrongThang, HoTroVanChuyen
					FROM #tblMonthlyDiscountReportByCategory

					UPDATE #tblMonthlyDiscountReport
					SET [ThuongCKTheoCT] = DoanhSoTrongThang * 10 / 100

					DROP TABLE #tblMonthlyDiscountReportByCategory		
				END

			ELSE 
				BEGIN 


					UPDATE #tblMonthlyDiscountReport
					SET 
					 [DS-TD] = (CASE WHEN CHARINDEX('-TD', PhanLoaiHang) > 0 THEN DoanhSoTrongThang ELSE NULL END )
					,[DS-CC] = (CASE WHEN CHARINDEX('-CC', PhanLoaiHang) > 0 THEN DoanhSoTrongThang ELSE NULL END )
					,[CK-TD] = (SELECT TOP 1 (DoanhSoTrongThang * ISNULL(MucThuong, 0) / 100) 
									FROM #tblMonthlyDiscountReport tmdr
									INNER JOIN #tblDiscount td ON tmdr.PhanLoaiHang LIKE '%' + td.SanPhamID + '%'
									WHERE tmdr.ID = #tblMonthlyDiscountReport.ID AND CHARINDEX('-TD', tmdr.PhanLoaiHang) > 0 AND tmdr.SoLuong >= td.DoanhSo)
					,[CK-CC] = (SELECT TOP 1 (DoanhSoTrongThang * ISNULL(MucThuong, 0) / 100) 
									FROM #tblMonthlyDiscountReport tmdr
									INNER JOIN #tblDiscount td ON tmdr.PhanLoaiHang LIKE '%' + td.SanPhamID + '%'
									WHERE tmdr.ID = #tblMonthlyDiscountReport.ID AND CHARINDEX('-CC', tmdr.PhanLoaiHang) > 0 AND tmdr.SoLuong >= td.DoanhSo)
					,[ThanhToanTrongThang] = [TienThanhToan]
					,[ChietKhauThanhToanTienNhanh] = ( CASE WHEN ([TienThanhToan] >=TienChuaThanhToan AND DATEDIFF(hour, NgayHoaDon, NgayThanhToan) < 24) THEN DoanhSoTrongThang * 5 / 100 ELSE 0 END ) 
					,[HoTroKhac] = 0
					,[ThuongCKTheoCT] = (SELECT TOP 1 (DoanhSoTrongThang * ISNULL(MucThuong, 0) / 100) 
									FROM #tblMonthlyDiscountReport tmdr
									INNER JOIN #tblDiscount td ON tmdr.PhanLoaiHang LIKE '%' + td.SanPhamID + '%'
									WHERE tmdr.ID = #tblMonthlyDiscountReport.ID)

				END

			UPDATE #tblMonthlyDiscountReport
			SET [TongGiamTru] = ISNULL([CK-TD], 0) + ISNULL([CK-CC], 0) + ISNULL([ChietKhauThanhToanTienNhanh], 0) + ISNULL([HoTroVanChuyen], 0) + ISNULL([HoTroKhac], 0)
				,[ThanhToanNhanhTheoCT] = (ISNULL([DS-TD], 0) + ISNULL([DS-CC], 0) ) * 5 / 100


			INSERT INTO #tblTongKetCacChuongTrinh(KhachHangID, MaKH, TenKH)
			SELECT DISTINCT discountReport.KhachHangID, discountReport.MaKH, discountReport.TenKH
			FROM #tblMonthlyDiscountReport discountReport
			WHERE discountReport.KhachHangID NOT IN (SELECT tongket.KhachHangID FROM #tblTongKetCacChuongTrinh tongket)

			UPDATE #tblTongKetCacChuongTrinh
			SET ChietKhauThangTheoCT = ISNULL(ChietKhauThangTheoCT, 0) + ISNULL((SELECT SUM(ISNULL([CK-TD], 0) + ISNULL([CK-CC], 0)) FROm #tblMonthlyDiscountReport discount WHERE discount.KhachHangID = #tblTongKetCacChuongTrinh.KhachHangID), 0)
			, ThanhToanNhanhTheoChuongTrinh = ISNULL(ThanhToanNhanhTheoChuongTrinh, 0) + ISNULL((SELECT SUM(ISNULL([ThanhToanNhanhTheoCT], 0)) FROm #tblMonthlyDiscountReport discount WHERE discount.KhachHangID = #tblTongKetCacChuongTrinh.KhachHangID), 0)
--
--			SELECT KhachHangID, MaKH, TenKH
--					,@TrungTamName as TrungTamName 
--					, SUM([DS-TD]) AS [DS-TD], SUM([DS-CC]) AS [DS-CC]
--					, SUM([CK-TD]) as [CK-TD], SUM([CK-CC]) as [CK-CC]
--					, SUM(ThanhToanTrongThang) as [ThanhToanTrongThang], SUM(ChietKhauThanhToanTienNhanh) AS [ChietKhauThanhToanTienNhanh], SUM(HoTroVanChuyen) as HoTroVanChuyen
--					, SUM(HoTroKhac) as HoTroKhac
--					, SUM(TongGiamTru) as TongGiamTru
--					, SUM([ThanhToanNhanhTheoCT]) as [ThanhToanNhanhTheoCT]
--					, SUM([ThuongCKTheoCT]) as [ThuongCKTheoCT]
--					, (SELECT TOP 1 ChuongTrinhChietKhau FROM #tblDiscount) as ChuongTrinhChietKhau FROM #tblMonthlyDiscountReport
--			GROUP BY KhachHangID, MaKH, TenKH

			DROP TABLE #tblMonthlyDiscountReport

			DROP TABLE #tblDiscount
			FETCH NEXT FROM ChietKhauCursor
			INTO @ChietKhauID
	END

	CLOSE ChietKhauCursor;
	DEALLOCATE ChietKhauCursor;

	--FUCK, xong cai chiet khau o day ...
	UPDATE #tblTongKetCacChuongTrinh
	SET ChuongTrinhMayPhaMauViTinh
    SELECT @TrungTamName AS TenTrungTam,
    kh.KhachHangID, kh.MaKH, kh.TenKH, kh.LEVEL, 
    khdt.Duration, khdt.StartDate, khdt.MucChietKhauTD, khdt.MucChietKhauCC,
    dsck.DoanhSoCamKet, dsck.SoTienLapQuy,
    rev.DoanhSoTD, rev.DoanhSoCC,
    th.BrandName AS TenThuongHieu,
    pt.NAME AS TenPhanThuong,
    pt.Value AS GiaTriPhanThuong
    FROM dbo.KhachHangDuthuong khdt
    LEFT JOIN vwGetTotalMonthlyRevenue rev ON khdt.HoiVienID = rev.KhachHangID
    JOIN vwALlCustomers kh ON kh.KhachHangID = khdt.HoiVienID
    JOIN ThuongHieu th ON khdt.BrandID = th.BrandID
    LEFT JOIN PhanThuong pt ON khdt.PhanThuongID = pt.ID
    LEFT JOIN (SELECT KhachHangDuThuongID, SUM(Doanhso) AS DoanhSoCamKet, SUM(DoanhSoLapQuy) AS SoTienLapQuy FROM DoanhSoCamKet GROUP BY KhachHangDuthuongID) dsck ON dsck.KhachHangDuThuongID = khdt.ID
    WHERE rev.Thang = @Month AND rev.Nam = @Year AND ISNULL(rev.ThuongHieu, '') = @BrandCode
    AND kh.BranchID = @BranchID
    AND khdt.Duration = 1 AND khdt.IsActive = 1
    AND khdt.StartDate <= @vReportDate
	AND DATEADD(mm, khdt.Duration, khdt.StartDate) > @vReportDate

	SELECT * FROM #tblTongKetCacChuongTrinh
	DROP TABLE #tblTongKetCacChuongTrinh
END

--EXEC [ATA].[dbo].[sprTongHopCacChuongTrinhDaiLy] '5cf2df83-3799-43fe-aaf5-04968c57f029', 2010, 04